package com.jeesite.modules.stock.dao;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.jeesite.common.entity.Page;
import com.jeesite.common.lang.StringUtils;
import com.jeesite.common.utils.ClassUtils;
import com.jeesite.modules.stock.dao.mapper.SectorDailyMapper;
import com.jeesite.modules.stock.dto.SectorDateRangeRankSearchDTO;
import com.jeesite.modules.stock.dto.SectorKlineQueryDTO;
import com.jeesite.modules.stock.dto.SectorSearchDTO;
import com.jeesite.modules.stock.entity.SectorDailyEntity;
import com.jeesite.modules.stock.entity.StockSectorEntity;

@Component
public class GroupDailyDataDao {
	
	@Autowired
	DataSource dataSource;
	
	@Autowired
	private StockSectorDao stockSectorDao;
	
	@Autowired
	private SectorDailyMapper sectorDailyMapper;

	private JdbcTemplate getJdbcTemplate() {
		return new JdbcTemplate(dataSource);
	}
	
	private String buildSearchSql(SectorSearchDTO searchDTO, boolean countSql) {
		String sql="select %s from group_daily_data as a ";
		String where = "";
		if(!StringUtils.isBlank(searchDTO.getKeyword())) {
			where += "a.group_name like '%"+searchDTO.getKeyword()+"%'";
		}
		if(!"".equals(where)) {
			sql+=" where "+where;
		}
		int pageNo = searchDTO.getPageNo()==null?1:searchDTO.getPageNo();
		int pageSize = searchDTO.getPageSize()==null?12:searchDTO.getPageSize();
		
		int offset = (pageNo-1) * pageSize;
		
		String order = searchDTO.getOrder()==null ? "desc" : searchDTO.getOrder();
		String orderBy = searchDTO.getOrderBy()==null ? "rise_and_fall": searchDTO.getOrderBy();
		
		if(!countSql) {
			sql = String.format(sql, "a.*");
			sql += " order by "+orderBy+" "+order;
			sql += " limit "+offset+","+pageSize;
		} else {
			sql = String.format(sql, "count(1)");
		}
		return sql;
	}
	
	/**
	 * 分页查询板块数据
	 * 
	 * @param searchDTO 查询参数
	 * @return
	 */
	
	public Page<Map<String, Object>> findPage(SectorSearchDTO searchDTO) {
		JdbcTemplate jdbcTempdate = getJdbcTemplate();
		List<Map<String, Object>> list = jdbcTempdate.query(buildSearchSql(searchDTO, false),
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						ResultSetMetaData metaData = rs.getMetaData();
						Map<String, Object> data = new HashMap<String, Object>();
						for (int i = 1; i <= metaData.getColumnCount(); i++) {
							Object value = rs.getObject(i);
							String columnName = metaData.getColumnName(i);
							data.put(ClassUtils.camelCharset(columnName), value);
						}
						return data;
					}
		});
		int total = findCount(searchDTO);
		
		Page<Map<String, Object>> page = new Page<Map<String,Object>>(searchDTO.getPageNo(), searchDTO.getPageSize());
		page.setList(list);
		page.setCount(total);
		
		return page;
	}

	/**
	 * 查询总记录数
	 * 
	 * @param stockSearchDTO
	 * @return
	 */
	public int findCount(SectorSearchDTO searchDTO) {
		JdbcTemplate jdbcTempdate = getJdbcTemplate();
		String sql = buildSearchSql(searchDTO, true);
		return jdbcTempdate.queryForObject(sql, Integer.class);
	}
	
	/**
	 * 按时间查询板块区间排名
	 * 
	 * @param searchDTO 查询参数
	 * @return
	 */
	public Page<Map<String, Object>> findDateRangeRank(SectorDateRangeRankSearchDTO searchDTO) {
		String columns = "a.id, a.group_name, a.close,("
				+ "	select (c2.close - c1.close)*100/c1.`close` mix from "
				+ "	(select * from group_daily_data where date='#startDate' and group_name=a.group_name) as c1,"
				+ "	(select * from group_daily_data where date='#endDate' and group_name=a.group_name) as c2 "
				+ ") as rise_and_fall ";
		String sql="select #columns "
				+ "from group_daily_data as a "
				+ "where a.date='#endDate' #keyword #sectorIds order by rise_and_fall desc ";
		List<StockSectorEntity> sectors = null;
		if(!StringUtils.isBlank(searchDTO.getStockCode())) {
			sectors = stockSectorDao.findByStockCode(searchDTO.getStockCode());
		} 
		if(sectors==null || sectors.isEmpty()){
			sql = sql.replaceAll("#sectorIds", "");
		} else {
			String sectorNames = sectors.stream().map(item->"'"+item.getSectorName()+"'")
					.collect(Collectors.joining(","));
			sql = sql.replaceAll("#sectorIds", " and a.group_name in("+sectorNames+")");
		}
		
		sql = sql.replaceAll("#startDate", searchDTO.getStartDate())
				.replaceAll("#endDate", searchDTO.getEndDate());
		if(!StringUtils.isBlank(searchDTO.getKeyword())) {
			sql = sql.replaceAll("#keyword", "(and a.group_name like '"+searchDTO.getKeyword()+"%'");
		} else {
			sql = sql.replaceAll("#keyword", "");
		}

		String querySql = sql.replaceAll("#columns", columns.replaceAll("#startDate", searchDTO.getStartDate())
				.replaceAll("#endDate", searchDTO.getEndDate()));
		String countSql = sql.replaceAll("#columns", "count(1)");
		int pageNo = searchDTO.getPageNo()==null ? 1 : searchDTO.getPageNo();
		int pageSize = searchDTO.getPageSize()==null? 12 : searchDTO.getPageSize();
		querySql += "limit "+((pageNo-1)*pageSize)+","+pageSize;
		
		JdbcTemplate jdbcTempdate = getJdbcTemplate();
		List<Map<String, Object>> list = jdbcTempdate.query(querySql,
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						ResultSetMetaData metaData = rs.getMetaData();
						Map<String, Object> data = new HashMap<String, Object>();
						for (int i = 1; i <= metaData.getColumnCount(); i++) {
							Object value = rs.getObject(i);
							String columnName = metaData.getColumnName(i);
							data.put(ClassUtils.camelCharset(columnName), value);
						}
						return data;
					}
		});
		
		int total = jdbcTempdate.queryForObject(countSql, Integer.class);
		
		Page<Map<String, Object>> page = new Page<Map<String,Object>>(pageNo, pageSize);
		page.setCount(total);
		page.setList(list);
		
		return page;
	}
	/**
	 * 查询板块数据
	 * @param groupName
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<SectorDailyEntity> findList(String groupName, String startDate, String endDate) {
		SectorKlineQueryDTO param = new SectorKlineQueryDTO();
		param.setGroupName(groupName);
		param.setStartDate(startDate);
		param.setEndDate(endDate);
		return sectorDailyMapper.findSectorList(param);
	}
}
